﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using EntityObjects;

namespace DAL_V1.App_Code.DBBroker
{
    public class RequisitionDBBroker
    {
        private  SSISDataSet ds;
        private  SSISDataSetTableAdapters.RequisitionTableAdapter tda;
        private  SSISDataSetTableAdapters.RequisitionDetailsTableAdapter detailstda;
        private SSISDataSetTableAdapters.ConsolidateRetrivalTableAdapter consolidatetda;
        private SSISDataSetTableAdapters.RetrivalTableAdapter retrivaltda;

        private int index;
        private List<Requisition> req_list;
        private List<RequisitionDetails> reqDetails_list;
        private Requisition req;
        private RequisitionDetails reqDetails;
        private SSISDataSet.RequisitionDataTable req_td;
        private SSISDataSet.RequisitionDetailsDataTable reqDetails_td;
        private SSISDataSet.ConsolidateRetrivalDataTable consolidateRetrival_td;
        private SSISDataSet.RetrivalDataTable retrival_td;

        //singleton
        private static bool instance_flag = false;
        private static RequisitionDBBroker instance;

        private RequisitionDBBroker()
        {
            ds = new SSISDataSet();
            tda = new SSISDataSetTableAdapters.RequisitionTableAdapter();
            detailstda = new SSISDataSetTableAdapters.RequisitionDetailsTableAdapter();
            consolidatetda = new SSISDataSetTableAdapters.ConsolidateRetrivalTableAdapter();
            retrivaltda = new SSISDataSetTableAdapters.RetrivalTableAdapter();

            //tda.Fill(ds.Requisition);
            //detailstda.Fill(ds.RequisitionDetails);
        }

        public static RequisitionDBBroker getRequisitionDBBroker()
        {
            if (!instance_flag)
            {
                instance = new RequisitionDBBroker();
                instance_flag = true;

                return instance;
            }
            else
            {
                return instance;
            }
        }

        public List<Requisition> selectAllRequisition()
        {
            tda.Fill(ds.Requisition);
            req_list = new List<Requisition>();
            foreach (SSISDataSet.RequisitionRow row in ds.Requisition.Rows)
            {
                req = new Requisition();
                req.ReqFormId = row.ReqFormId;
                req.DeptId = row.DeptId;
                req.EmpId = row.EmpId;
                req.RequestedDate = Convert.ToString(row.RequestedDate);
                req.Status = row.Status;
                req.Remarks = row.Remarks;

                if (!(row.IsNull("ApprovedOn")))
                {
                    req.ApprovedOn = Convert.ToString(row.ApprovedOn);
                }
                req_list.Add(req);
            }
            return req_list;
        }

        public int addRequisition(Requisition rq)
        {                       
            int count=tda.Insert(rq.DeptId, rq.EmpId, DateTime.Today, rq.Status, rq.Remarks, null);
            int newReqFormId = getNewSerialNo();
            foreach (RequisitionDetails rd in rq.ReqDetails_list)
            {
                detailstda.Insert(newReqFormId, rd.ItemId, rd.RequestedQty, 0);
            }
            return count;
        }

        public int getNewSerialNo()
        {
            SSISDataSetTableAdapters.RequisitionNewSerialNoTableAdapter tda = new SSISDataSetTableAdapters.RequisitionNewSerialNoTableAdapter();
            tda.Fill(ds.RequisitionNewSerialNo);
            SSISDataSet.RequisitionNewSerialNoRow row = (SSISDataSet.RequisitionNewSerialNoRow)ds.RequisitionNewSerialNo.Rows[0];
            int newx = 1;
            if (row.IsSerialNoNull())
            {
                return newx;
            }
            else
            {
                return row.SerialNo;
            }
        }

        //private int getIndex(int Id)
        //{
        //    SSISDataSet.RequisitionRow row = ds.Requisition.FindByReqFormId(Id);
        //    return ds.Requisition.Rows.IndexOf(row);
        //}

        private SSISDataSet.RequisitionRow getRowById(int Id)
        {
            tda.Fill(ds.Requisition);
            return ds.Requisition.FindByReqFormId(Id);
        }

        public int updateRequisition(Requisition rq, int Id)
        {
            SSISDataSet.RequisitionRow row = getRowById(Id);
            if (row != null)
            {
                if (rq.DeptId != null && !(rq.DeptId.Equals("")))
                {
                    row.DeptId = rq.DeptId;
                }
                if (rq.Status != null && !(rq.Status.Equals("")))
                {
                    row.Status = rq.Status;
                }
                if (rq.ApprovedOn != null && !(rq.ApprovedOn.Equals("")))
                {
                    row.ApprovedOn = Convert.ToDateTime(rq.ApprovedOn);
                }
                if (rq.Remarks != null && !(rq.Remarks.Equals("")))
                {
                    row.Remarks = rq.Remarks;
                }
            }

            ds.EndInit();
            return tda.Update(ds.Requisition);
        }

        public int deleteRequisition(int Id)
        {
            detailstda.DeleteByReqFormId(Id);
            return tda.DeleteByReqFormId(Id);
        }

        public Requisition selectRequisition(int Id)
        {
            tda.Fill(ds.Requisition);
            SSISDataSet.RequisitionRow r = getRowById(Id);
            if (r != null)
            {
                req = new Requisition();
                req.ReqFormId = r.ReqFormId;
                req.DeptId = r.DeptId;
                req.EmpId = r.EmpId;
                req.RequestedDate = Convert.ToString(r.RequestedDate);
                req.Status = r.Status;
                if (!(r.IsApprovedOnNull()))
                {
                    req.ApprovedOn = Convert.ToString(r.ApprovedOn);
                }
                req.Remarks = r.Remarks;

                //Details
                reqDetails_list = selectReqDetailsByReqId(r.ReqFormId);
                req.ReqDetails_list = reqDetails_list;

                return req;
            }
            else
            {
                return null;
                
            }
        }

        ////////////Geeta////////////////////////////////////////////

        public List<Requisition> getRequisitionbyStatusAndDate(String status, DateTime fromDate, DateTime toDate, String empId)
        {
            tda.Fill(ds.Requisition);
           req_td = tda.GetDataByStatusAndDate(status, fromDate, toDate, empId);
           if (req_td.Rows.Count > 0)
           {
               req_list = new List<Requisition>();
               foreach (SSISDataSet.RequisitionRow row in req_td.Rows)
               {
                   Requisition r = new Requisition();
                   r.ReqFormId = row.ReqFormId;
                   r.DeptId = row.DeptId;
                   r.EmpId = row.EmpId;
                   r.RequestedDate = Convert.ToString(row.RequestedDate);
                   r.Status = row.Status;
                   if (!(row.IsNull("ApprovedOn")))
                   {
                       r.ApprovedOn = Convert.ToString(row.ApprovedOn);
                   }
                   req_list.Add(r);
               }

               return req_list;
           }
           else
           {
               return null;
           }
        }


        public List<Requisition> getRequisitionByDate(DateTime fromDate, DateTime toDate, String empId)
        {
            tda.Fill(ds.Requisition);
            req_td = tda.GetDataByDate(fromDate, toDate,empId);
            if (req_td.Rows.Count > 0)
            {
                req_list = new List<Requisition>();
                foreach (SSISDataSet.RequisitionRow row in req_td.Rows)
                {
                    Requisition r = new Requisition();
                    r.ReqFormId = row.ReqFormId;
                    r.DeptId = row.DeptId;
                    r.EmpId = row.EmpId;
                    r.RequestedDate = Convert.ToString(row.RequestedDate);
                    r.Status = row.Status;
                    if (!(row.IsNull("ApprovedOn")))
                    {
                        r.ApprovedOn = Convert.ToString(row.ApprovedOn);
                    }
                    req_list.Add(r);
                }

                return req_list;
            }
            else
            {
                return null;
            }
        }

        public List<Requisition> getRequisitionByStatus(String Status,String empId)
        {
            tda.Fill(ds.Requisition);
            req_td = tda.GetDataByStatusAndEmpId(Status,empId);
            if (req_td.Rows.Count > 0)
            {
                req_list = new List<Requisition>();
                foreach (SSISDataSet.RequisitionRow row in req_td.Rows)
                {
                    Requisition r = new Requisition();
                    r.ReqFormId = row.ReqFormId;
                    r.DeptId = row.DeptId;
                    r.EmpId = row.EmpId;
                    r.RequestedDate = Convert.ToString(row.RequestedDate);
                    r.Status = row.Status;
                    if (!(row.IsNull("ApprovedOn")))
                    {
                        r.ApprovedOn = Convert.ToString(row.ApprovedOn);
                    }
                    req_list.Add(r);
                }

                return req_list;
            }
            else
            {
                return null;
            }
        }

        public List<RequisitionItems> getRequisitionItemsbyReqFormId(int reqFormId)
        {
            SSISDataSetTableAdapters.RequisitionItemsTableAdapter tda = new SSISDataSetTableAdapters.RequisitionItemsTableAdapter();
            int j = tda.Fill(ds.RequisitionItems, reqFormId);
            if (j > 0)
            {
                List<RequisitionItems> list = new List<RequisitionItems>();
                foreach (SSISDataSet.RequisitionItemsRow row in ds.RequisitionItems.Rows)
                {
                    RequisitionItems rt = new RequisitionItems();
                    rt.ItemId = row.ItemId;
                    rt.ItemName = row.Description;
                    rt.RequestedQty = row.RequestedQty;
                    rt.ReceivedQty = row.ReceivedQty;
                    rt.UOM = row.UOM;
                    rt.CategoryName = row.CategoryName;
                    list.Add(rt);
                }

                return list;
            }
            else
            {
                return null;
            }
        }


        public Requisition getRequsitionByEmpIdAndAppliedStatus(String empId)
        {
            tda.Fill(ds.Requisition);
            req_td = tda.GetDataByEmpIdAndStatus(empId, "Applied");

            if (req_td.Rows.Count > 0)
            {
                Requisition r = new Requisition();
                SSISDataSet.RequisitionRow row = (SSISDataSet.RequisitionRow)req_td.Rows[0];
                r.ReqFormId = row.ReqFormId;
                r.DeptId = row.DeptId;
                r.EmpId = row.EmpId;
                r.RequestedDate = Convert.ToString(row.RequestedDate);
                r.Status = row.Status;
                return r;
            }
            else
            {
                return null;
            }
        }

        public List<Requisition> getRequisitionByEmpId(String empId)
        {
            tda.Fill(ds.Requisition);
            req_td = tda.GetDataByEmpId(empId);

            if (req_td.Rows.Count > 0)
            {
                req_list = new List<Requisition>();
                Requisition r = new Requisition();
                foreach (SSISDataSet.RequisitionRow row in req_td.Rows)
                {
                    req = new Requisition();
                    req.ReqFormId = row.ReqFormId;
                    req.DeptId = row.DeptId;
                    req.EmpId = row.EmpId;
                    req.RequestedDate = Convert.ToString(row.RequestedDate);
                    req.Status = row.Status;
                    req.Remarks = row.Remarks;

                    if (!(row.IsNull("ApprovedOn")))
                    {
                        req.ApprovedOn = Convert.ToString(row.ApprovedOn);
                    }
                    req_list.Add(req);
                }

                return req_list;
            }
            else
            {
                return null;
            }
        }


        ///////////////Geeta////////////////////////////////////////

        /////////////////////Swetha////////////////////////////////////

        public List<Requisition> getRequistionByAppliedStatus()
        {
            
            SSISDataSetTableAdapters.EmployeeRequisitionByStatusTableAdapter tda = new SSISDataSetTableAdapters.EmployeeRequisitionByStatusTableAdapter();
            int j = tda.Fill(ds.EmployeeRequisitionByStatus, "Applied");

            if (j > 0)
            {
                req_list = new List<Requisition>();

                foreach(SSISDataSet.EmployeeRequisitionByStatusRow row in ds.EmployeeRequisitionByStatus.Rows)
                {
                     Requisition r = new Requisition();
                    r.ReqFormId = row.ReqFormId;
                    r.DeptId = row.DeptId;
                    r.EmpId = row.EmpId;
                    r.RequestedDate = Convert.ToString(row.RequestedDate);
                    r.Status = row.Status;
                    if (!(row.IsNull("ApprovedOn")))
                    {
                        r.ApprovedOn = Convert.ToString(row.ApprovedOn);
                    }
                    r.EmpName = row.EmpName;
                    r.Remarks = row.Remarks;

                    req_list.Add(r);
                }

                return req_list;
            }

            else
            {
                return null;
            }
        }

        public List<Requisition> getRequisitionByDeptIdAndApprovedStatus(String deptId)
        {
            tda.Fill(ds.Requisition);
            req_td = tda.GetDataByDeptIdAndStatus(deptId, "Approved");
            if (req_td.Rows.Count > 0)
            {
                req_list = new List<Requisition>();
                foreach (SSISDataSet.RequisitionRow row in req_td.Rows)
                {
                    req = new Requisition();
                    req.ReqFormId = row.ReqFormId;
                    req.DeptId = row.DeptId;
                    req.EmpId = row.EmpId;
                    req.RequestedDate = Convert.ToString(row.RequestedDate);
                    req.Status = row.Status;
                    req.Remarks = row.Remarks;

                    if (!(row.IsNull("ApprovedOn")))
                    {
                        req.ApprovedOn = Convert.ToString(row.ApprovedOn);
                    }
                    req_list.Add(req);
                }

                return req_list;
                }
                else
                {
                    return null;
                }
        }

        public List<Requisition> getRequisitionByDeptIdAndAppliedStatus(String deptId)
        {
            tda.Fill(ds.Requisition);
            req_td = tda.GetDataByDeptIdAndStatus(deptId, "Applied");
            if (req_td.Rows.Count > 0)
            {
                req_list = new List<Requisition>();
                foreach (SSISDataSet.RequisitionRow row in req_td.Rows)
                {
                    req = new Requisition();
                    req.ReqFormId = row.ReqFormId;
                    req.DeptId = row.DeptId;
                    req.EmpId = row.EmpId;
                    Employee emp = EmployeeDBBroker.getEmployeeDBBroker().selectEmployee(row.EmpId);
                    req.EmpName = emp.EmpName;
                    req.RequestedDate = Convert.ToString(row.RequestedDate);
                    req.Status = row.Status;
                    req.Remarks = row.Remarks;

                    if (!(row.IsNull("ApprovedOn")))
                    {
                        req.ApprovedOn = Convert.ToString(row.ApprovedOn);
                    }
                    req_list.Add(req);
                }

                return req_list;
            }
            else
            {
                return null;
            }
        }

        public List<Department> getRequestedDepartmentByApprovedStatus()
        {
            SSISDataSetTableAdapters.RequestedDepartmentTableAdapter tda = new SSISDataSetTableAdapters.RequestedDepartmentTableAdapter();
            int j = tda.Fill(ds.RequestedDepartment);
            if (j > 0)
            {
                SSISDataSet.RequestedDepartmentDataTable td = tda.GetDataByStatus("Approved");
                if (td.Rows.Count > 0)
                {
                    List<Department> dept_list = new List<Department>();
                    foreach (SSISDataSet.RequestedDepartmentRow row in td.Rows)
                    {
                        Department d = new Department();
                        d.DeptId = row.DeptId;
                        d.DeptName = row.DeptName;
                        d.NOofRequested = row.NOofReq;
                        dept_list.Add(d);
                    }
                    return dept_list;
                }
                else
                {
                    return null;
                }
            }
            else
            {
                return null;
            }
        }


        public List<Category> getRequestedCategorybyApprovedStatus(String deptId)
        {
            SSISDataSetTableAdapters.RequestedCategoryTableAdapter tda = new SSISDataSetTableAdapters.RequestedCategoryTableAdapter();
            int j = tda.Fill(ds.RequestedCategory);
            if (j > 0)
            {
                SSISDataSet.RequestedCategoryDataTable dt = tda.GetDataByStatusAndDept("Approved",deptId);
                if (dt.Rows.Count > 0)
                {
                    List<Category> category_list = new List<Category>();
                    foreach (SSISDataSet.RequestedCategoryRow row in dt.Rows )
                    {
                        Category c = new Category();
                        c.CategoryId = row.CategoryId;
                        c.CategoryName = row.CategoryName;
                        c.ItemCount = row.itemCount;
                        category_list.Add(c);
                    }
                    return category_list;
                }
                else
                {
                    return null;
                }
            }
            else
            {
                return null;
            }
        }

        public List<Category> getRequestedCategorybyApprovedStatus()
        {
            SSISDataSetTableAdapters.RequestedCategoryTableAdapter tda = new SSISDataSetTableAdapters.RequestedCategoryTableAdapter();
            int j = tda.Fill(ds.RequestedCategory);
            if (j > 0)
            {
                SSISDataSet.RequestedCategoryDataTable dt = tda.GetDataByApprovedStatus();
                if (dt.Rows.Count > 0)
                {
                    List<Category> category_list = new List<Category>();
                    foreach (SSISDataSet.RequestedCategoryRow row in dt.Rows )
                    {
                        Category c = new Category();
                        c.CategoryId = row.CategoryId;
                        c.CategoryName = row.CategoryName;
                        c.ItemCount = row.itemCount;
                        category_list.Add(c);
                    }
                    return category_list;
                }
                else
                {
                    return null;
                }
            }
            else
            {
                return null;
            }
        }


        public int getRequestedCountByApprovedStatus()
        {
            SSISDataSetTableAdapters.RequestedCountTableAdapter tda = new SSISDataSetTableAdapters.RequestedCountTableAdapter();
            int j = tda.Fill(ds.RequestedCount);
            if (j > 0)
            {
                SSISDataSet.RequestedCountDataTable td = tda.GetDataByStatus("Approved");
                if (td.Rows.Count > 0)
                {
                    SSISDataSet.RequestedCountRow row = (SSISDataSet.RequestedCountRow)td.Rows[0];
                    return row.RequestedCount;
                }
                else
                {
                    return 0;
                }
            }
            else
            {
                return 0;
            }
        }

        /////////////////////////Swetha//////////////////////////////////

        //Requistion Details
        public List<RequisitionDetails> selectAllRequisitionDetails()
        {
            detailstda.Fill(ds.RequisitionDetails);
            reqDetails_list = new List<RequisitionDetails>();
            for (int i = 0; i < ds.RequisitionDetails.Rows.Count; i++)
            {
                reqDetails = new RequisitionDetails();
                reqDetails.ReqFormId = ds.RequisitionDetails[i].ReqFormId;
                reqDetails.ItemId = ds.RequisitionDetails[i].ItemId;
                reqDetails.RequestedQty = ds.RequisitionDetails[i].RequestedQty;
                reqDetails.ReceivedQty = ds.RequisitionDetails[i].ReceivedQty;
                reqDetails_list.Add(reqDetails);
            }
            return reqDetails_list;
        }

        public int addRequisitionDetails(RequisitionDetails rqDetails)
        {
            SSISDataSet.RequisitionDetailsRow newRow = ds.RequisitionDetails.NewRequisitionDetailsRow();
            newRow.ReqFormId = rqDetails.ReqFormId;
            newRow.ItemId = rqDetails.ItemId;
            newRow.RequestedQty = rqDetails.RequestedQty;
            newRow.ReceivedQty = rqDetails.ReceivedQty;

            ds.RequisitionDetails.Rows.Add(newRow);
            return detailstda.Update(ds.RequisitionDetails);
        }

        //private int getIndex(int formId, String itemId)
        //{
        //    SSISDataSet.RequisitionDetailsRow row = ds.RequisitionDetails.FindByReqFormIdItemId(formId, itemId);
        //    return ds.RequisitionDetails.Rows.IndexOf(row);
        //}

        private SSISDataSet.RequisitionDetailsRow getRowById(int formId, String itemId)
        {
            detailstda.Fill(ds.RequisitionDetails);
            return ds.RequisitionDetails.FindByReqFormIdItemId(formId, itemId);
        }

        public int updateRequisitionDetails(RequisitionDetails rqDetails, int formId, String itemId)
        {
            SSISDataSet.RequisitionDetailsRow row = getRowById(formId, itemId);
            if (row != null)
            {
                if (rqDetails.RequestedQty != null && rqDetails.RequestedQty != 0)
                {
                    row.RequestedQty = rqDetails.RequestedQty;
                }
                if (rqDetails.ReceivedQty != null && rqDetails.ReceivedQty != 0)
                {
                    row.ReceivedQty = rqDetails.ReceivedQty;
                }
            }
           
            return detailstda.Update(ds.RequisitionDetails);
        }

        public int deleteRequisitionDetails(int formId,String itemId)
        {
            return detailstda.DeleteByFormIdAndItemId(formId, itemId);
        }

        public RequisitionDetails selectRequisitionDetails(int formId,String itemId)
        {
            detailstda.Fill(ds.RequisitionDetails);
            SSISDataSet.RequisitionDetailsRow r = getRowById(formId, itemId);
            if (r != null)
            {
                reqDetails = new RequisitionDetails();
                reqDetails.ReqFormId = r.ReqFormId;
                reqDetails.ItemId = r.ItemId;
                reqDetails.RequestedQty = r.RequestedQty;
                reqDetails.ReceivedQty = r.ReceivedQty;
                return reqDetails;
            }
            else
            {
                return null;
            }
        }

        public List<RequisitionDetails> selectReqDetailsByReqId(int id)
        {
            detailstda.Fill(ds.RequisitionDetails);
            reqDetails_td = detailstda.GetDataByReqId(id);

            if (reqDetails_td != null)
            {

                reqDetails_list = new List<RequisitionDetails>();
                for (int i = 0; i < reqDetails_td.Rows.Count; i++)
                {
                    reqDetails = new RequisitionDetails();
                    reqDetails.ReqFormId = reqDetails_td[i].ReqFormId;
                    reqDetails.ItemId = reqDetails_td[i].ItemId;
                    reqDetails.RequestedQty = reqDetails_td[i].RequestedQty;
                    reqDetails.ReceivedQty = reqDetails_td[i].ReceivedQty;

                    reqDetails_list.Add(reqDetails);
                }

                return reqDetails_list;
            }
            else
            {
                return null;
            }
        }


        public List<Retrival> getRequisitionByItemId(String itemId)
        {
            SSISDataSetTableAdapters.RequisitionByItemTableAdapter tda = new SSISDataSetTableAdapters.RequisitionByItemTableAdapter();
            int j = tda.Fill(ds.RequisitionByItem, itemId);
            if (j > 0)
            {
                List<Retrival> list = new List<Retrival>();
                foreach (SSISDataSet.RequisitionByItemRow row in ds.RequisitionByItem.Rows)
                {
                    Retrival r = new Retrival();
                    r.DeptId = row.DeptId;
                    r.RequestedQty = row.RequestedQty;
                    r.ReceivedQty = row.ReceivedQty;

                    list.Add(r);
                }

                return list;
            }
            else
            {
                return null;
            }
        }


        public Requisition getLastDataofGivenEmployee(String empId)
        {
            tda.Fill(ds.Requisition);
           req_td = tda.GetLastDatabyEmpId(empId);
           if (req_td.Rows.Count > 0)
           {
               SSISDataSet.RequisitionRow row = (SSISDataSet.RequisitionRow)req_td.Rows[0];
                req = new Requisition();
                req.ReqFormId = row.ReqFormId;
                req.DeptId = row.DeptId;
                req.EmpId = row.EmpId;
                req.RequestedDate = Convert.ToString(row.RequestedDate);
                req.Status = row.Status;
                if (!(row.IsApprovedOnNull()))
                {
                    req.ApprovedOn = Convert.ToString(row.ApprovedOn);
                }
                req.Remarks = row.Remarks;

                //Details
                reqDetails_list = selectReqDetailsByReqId(row.ReqFormId);
                req.ReqDetails_list = reqDetails_list;

                return req;
            }
            else
            {
                return null;
                
            }

        }

        //AMMT
        public List<ConsolidateRetrivalItem> getRequisitionDatabyItemId(String itemId)
        {
            consolidatetda.Fill(ds.ConsolidateRetrival,itemId);
            
            List<ConsolidateRetrivalItem> retrival_list = new List<ConsolidateRetrivalItem>();
            for (int i = 0; i < ds.ConsolidateRetrival.Rows.Count; i++)
            {
                ConsolidateRetrivalItem retrival_item = new ConsolidateRetrivalItem();
                retrival_item.TotalReqQty = ds.ConsolidateRetrival[i].ReqQty;
                retrival_item.TotalRecQty = ds.ConsolidateRetrival[i].RecQty;
                retrival_item.DeptName = ds.ConsolidateRetrival[i].DeptName;
                retrival_item.CategoryName = ds.ConsolidateRetrival[i].CategoryName;
                retrival_item.Desc = ds.ConsolidateRetrival[i].Description;
                retrival_item.DeptId = ds.ConsolidateRetrival[i].DeptId;

                retrival_list.Add(retrival_item);
            }
            return retrival_list;
        }

        public List<Retrival> getItemsForRetrival(int categoryId)
        {
            retrivaltda.Fill(ds.Retrival, categoryId);
          
            if (ds.Retrival.Rows.Count > 0)
            {
                List<Retrival> ret_list = new List<Retrival>();
                for (int i = 0; i < ds.Retrival.Rows.Count; i++)
                {
                    Retrival r = new Retrival();
                    r.ItemId = ds.Retrival[i].ItemId;
                    r.Desc = ds.Retrival[i].Description;
                    r.BinId = ds.Retrival[i].BinId;
                    r.RequestedQty = ds.Retrival[i].TotalReqQty;
                    r.ReceivedQty = ds.Retrival[i].TotalRecQty;
                    r.OutstandingQty = (ds.Retrival[i].TotalReqQty - ds.Retrival[i].TotalRecQty);
                    ret_list.Add(r);
                }

                return ret_list;
            }

            else
            {
                return null;
            }
        }

        //////////////Savithri/////////////////////////////////////////

        public List<RequisitionDetails> assignStationeryToEmployee(String deptId, String itemId)
        {
            SSISDataSetTableAdapters.AssignStationeryToEmployeeTableAdapter tda = new SSISDataSetTableAdapters.AssignStationeryToEmployeeTableAdapter();
            int j = tda.Fill(ds.AssignStationeryToEmployee, deptId, itemId);
            if (j > 0)
            {
                reqDetails_list = new List<RequisitionDetails>();
                foreach (SSISDataSet.AssignStationeryToEmployeeRow row in ds.AssignStationeryToEmployee.Rows)
                {
                    RequisitionDetails rd = new RequisitionDetails();
                    rd.EmpId = row.EmpId;
                    rd.EmpName = row.EmpName;
                    rd.RequestedQty = row.RequestedQty;
                    rd.OutStandingQty = row.outStandingQty;
                    reqDetails_list.Add(rd);
                }
                return reqDetails_list;
            }
            else
            {
                return null;
            }
        }


        public int getCurrentWeekRequestedQtyToAssignToEmp(String empId, String itemId)
        {
            SSISDataSetTableAdapters.RequestedQtyToAssignToEmpTableAdapter tda = new SSISDataSetTableAdapters.RequestedQtyToAssignToEmpTableAdapter();
            int j = tda.Fill(ds.RequestedQtyToAssignToEmp, empId, itemId);
            if (j > 0)
            {
                return ds.RequestedQtyToAssignToEmp[0].RequestedQty;
            }
            else
            {
                return 0;
            }
        }


        public int getOutstandingQtyToAssignToEmp(String empId, String itemId)
        {
            SSISDataSetTableAdapters.OutstandingQtyToAssignToEmpTableAdapter tda = new SSISDataSetTableAdapters.OutstandingQtyToAssignToEmpTableAdapter();
            int j = tda.Fill(ds.OutstandingQtyToAssignToEmp, empId, itemId);
            if (j > 0)
            {
                if (!(ds.OutstandingQtyToAssignToEmp[0].IsOutstandingQtyNull()))
                {
                    return ds.OutstandingQtyToAssignToEmp[0].OutstandingQty;
                }
                else
                {
                    return 0;
                }
            }
            else
            {
                return 0;
            }
        }

        public Requisition getRequsitionByEmpIdAndApprovedStatus(String empId)
        {
            tda.Fill(ds.Requisition);
            SSISDataSet.RequisitionDataTable reqDT=tda.GetDataByEmpIdAndStatus(empId, "Approved");

            if (reqDT.Rows.Count > 0)
            {
                Requisition r = new Requisition();
                SSISDataSet.RequisitionRow row = (SSISDataSet.RequisitionRow)reqDT.Rows[0];
                r.ReqFormId = row.ReqFormId;
                r.DeptId = row.DeptId;
                r.EmpId = row.EmpId;
                r.RequestedDate = Convert.ToString(row.RequestedDate);
                r.Status = row.Status;
                return r;
            }
            else
            {
                return null;
            }
        }

        ///////////////Savithri/////////////////////////////

        //////////////Geeta////////////////////////////////

        public List<RequisitionDetails> getRequisitionDetailsByEmpIdAndAppliedStatus(String empId)
        {
            SSISDataSetTableAdapters.RequisitionDetailsByEmpAndStatusTableAdapter tda = new SSISDataSetTableAdapters.RequisitionDetailsByEmpAndStatusTableAdapter();
            int j = tda.Fill(ds.RequisitionDetailsByEmpAndStatus, "Applied", empId);
            if (j > 0)
            {
                reqDetails_list = new List<RequisitionDetails>();
                foreach (SSISDataSet.RequisitionDetailsByEmpAndStatusRow row in ds.RequisitionDetailsByEmpAndStatus.Rows)
                {
                    RequisitionDetails rd = new RequisitionDetails();
                    rd.ReqFormId = row.ReqFormId;
                    rd.ItemId = row.ItemId;
                    rd.RequestedQty = row.RequestedQty;
                    rd.ReceivedQty = row.ReceivedQty;

                    reqDetails_list.Add(rd);
                }

                return reqDetails_list;
            }

            else
            {
                return null;
            }
        }

        ///////////////////Geeta////////////////////////////////

        
    }
}
